﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace Job.Core.Report
{
    /// <summary>
    /// Excel 操作封装(无法继承此类)。
    /// 注意：Excel 中所有索引下标均从1开始，而不是从0。
    /// </summary>
    public sealed class Excel : IDisposable
    {
        #region Properties

        /// <summary>
        /// 获取当前操作文件的全名。
        /// </summary>
        public string FileName { get; private set; }

        private Application app;
        private Workbook workBook;
        private Worksheet workSheet;    //当前操作的工作表。

        #endregion

        #region Constructor

        /// <summary>
        /// 初始化 Excel 类的新实例。
        /// </summary>
        public Excel()
        {
            app = new Application();
            app.Visible = false;
            workBook = app.Workbooks.Add(true);
            SetWorkSheet(1);    //默认设置第一个为当前工作表。
        }

        /// <summary>
        /// 初始化 Excel 类的新实例。
        /// </summary>
        /// <param name="fileName">要操作的 Excel 文件全名。</param>
        public Excel(string fileName)
        {
            FileName = fileName;
            app = new Application();
            app.Visible = false;
            workBook = app.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            SetWorkSheet(1);    //默认设置第一个为当前工作表。
        }

        #endregion

        #region Methods

        /// <summary>
        /// 设置当前工作表。
        /// </summary>
        /// <param name="index">索引号。</param>
        public void SetWorkSheet(int index)
        {
            workSheet = (Worksheet)workBook.Worksheets[index];
        }

        /// <summary>
        /// 设置当前工作表。
        /// </summary>
        /// <param name="sheetName">表名称。</param>
        public void SetWorkSheet(string sheetName)
        {
            workSheet = (Worksheet)workBook.Worksheets[sheetName];
        }

        /// <summary>
        /// 删除指定行。
        /// </summary>
        /// <param name="rowIndex">行号。</param>
        public void DeleteRow(int rowIndex)
        {
            Range rng = (Range)workSheet.Cells[rowIndex, 1];
            rng.EntireRow.Delete(XlDirection.xlUp);
        }

        /// <summary>
        /// 删除指定列。
        /// </summary>
        /// <param name="columnIndex">列号。</param>
        public void DeleteColumn(int columnIndex)
        {
            Range rng = (Range)workSheet.Cells[1, columnIndex];
            rng.EntireColumn.Delete(XlDirection.xlToLeft);
        }

        /// <summary>
        /// 向单元格中写入数据。
        /// </summary>
        /// <param name="rowIndex">行号。</param>
        /// <param name="columnIndex">列号。</param>
        /// <param name="content">写信内容。</param>
        public void WriteCell(int rowIndex, int columnIndex, string content)
        {
            this[rowIndex, columnIndex] = content;
        }

        /// <summary>
        /// 清除单元格内容。
        /// </summary>
        /// <param name="rowIndex">行号。</param>
        /// <param name="columnIndex">列号。</param>
        public void ClearCell(int rowIndex, int columentIndex)
        {
            this[rowIndex, columentIndex] = string.Empty;
        }

        #endregion

        #region Comment

        /// <summary>
        /// 写单元格批注。
        /// </summary>
        /// <param name="rowIndex">行号。</param>
        /// <param name="columnIndex">列号。</param>
        /// <param name="commentText">备注文本。</param>
        public void WriteComment(int rowIndex, int columnIndex, string commentText)
        {
            Range rng = (Range)workSheet.Cells[rowIndex, columnIndex];
            if (rng.Comment != null)
                rng.ClearComments();
            rng.AddComment(commentText);

        }

        /// <summary>
        /// 清除单元格血液。
        /// </summary>
        /// <param name="rowIndex">行号。</param>
        /// <param name="columnIndex">列号。</param>
        public void ClearComment(int rowIndex, int columnIndex)
        {
            Range rng = (Range)workSheet.Cells[rowIndex, columnIndex];
            rng.ClearComments();
        }

        #endregion

        #region Index

        /// <summary>
        /// 获取或设置指定行号、列号的值。
        /// </summary>
        /// <param name="rowIndex">行号。</param>
        /// <param name="columnIndex">列号。</param>
        /// <returns></returns>
        public string this[int rowIndex, int columnIndex]
        {
            get
            {
                Range rng = (Range)workSheet.Cells[rowIndex, columnIndex];
                return rng.Value2.ToString();
            }
            set
            {
                Range rng = (Range)workSheet.Cells[rowIndex, columnIndex];
                rng.Value2 = value;
            }
        }

        #endregion

        #region Save

        /// <summary>
        /// 保存所做修改。
        /// </summary>
        public void Save()
        {
            workBook.Save();
        }

        /// <summary>
        /// 另存为。
        /// </summary>
        /// <param name="fileName">要另存的文件全名。</param>
        public void SaveAs(string fileName)
        {
            workBook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        }

        #endregion

        #region ImportData

        /// <summary>
        /// 向 Excel 中导入数据。
        /// </summary>
        /// <param name="rows">数据行。</param>
        /// <param name="writeStartIndex">写入起始行。</param>
        public void ImportData(DataRow[] rows, int writeStartIndex)
        {
            for (int i = 0; i < rows.Length; i++)
            {
                DataRow row = rows[i];
                for (int j = 0; j < row.ItemArray.Length; j++)
                {
                    this[writeStartIndex + i + 1, j + 1] = row[j].ToString();
                }
            }
        }

        #endregion

        #region IDisposable Members

        private bool disposed;

        public void Dispose()
        {
            if (!disposed)
            {
                workBook.Close(false, Missing.Value, Missing.Value);
                app.Workbooks.Close();
                app.Quit();
                workSheet = null;
                workBook = null;
                app = null;
                GC.Collect();
                GC.SuppressFinalize(this);

                disposed = true;
            }
        }

        #endregion
    }
}